import pymysql


class Mysql:
    # 连接数据库 传入用户、密码等信息字典
    def SelectDatabase(db_info):
        host = db_info['address']
        user = db_info["user"]
        passwd = db_info["passwd"]
        port = db_info["port"]
        database = db_info["database"]
        try:
            conn = pymysql.connect(
                host=host,
                user=user,
                passwd=passwd,
                port=int(port),
                database=database,
                charset='utf8',
                cursorclass=pymysql.cursors.DictCursor
            )
            cur = conn.cursor()
            sql = db_info["sql"]
            cur.execute(sql)
            res = cur.fetchall()
            cur.close()
            conn.close()
            return res
        except Exception as e:

            return False

    # 获取所有数据库名并解析，传入用户、密码等信息字典
    def GetDatabase(db_info):
        # 获取所有数据库，排除自带的数据库
        exclude_list = ["sys", "information_schema", "devops", "performance_schema", "occ", "gogs", "mysql", "test_tm"]
        sql_database = "show databases"
        db_info["sql"] = sql_database
        db_info["database"] = "mysql"
        databases = Mysql.SelectDatabase(db_info)
        all_dbname = []

        for dbname_list in databases:
            db_name = dbname_list['Database']
            if db_name not in exclude_list:
                all_dbname.append(db_name)

        return all_dbname

    # 获取所有数据表名并解析，传入用户、密码等信息字典
    def GetTables(db_info, db_list):
        db_table = {}
        for db_pos in range(len(db_list)):
            table_list = []
            db_info["sql"] = "show tables"
            db_info["database"] = db_list[db_pos]
            tables_list = Mysql.SelectDatabase(db_info)
            for tables in tables_list:
                tablesinfo = "Tables_in_"+db_list[db_pos]
                tables = tables[tablesinfo]
                table_list.append(tables)
            db_table[db_list[db_pos]] = table_list
        return Mysql.DescTable(db_info, db_table)

    # 获取所有字段名并解析，传入用户、密码等信息字典
    def DescTable(db_info, db_table):
        db_info.update({"database": "mysql"})
        desc_tables = []
        i = 0
        for db in db_table.keys():
            all_dbtables = {}
            tableinfo_list = []
            table_set = db_table[db]
            i += 1
            for table_pos in range(len(table_set)):
                tableinfo = {}
                sql = "desc "+db+"."+table_set[table_pos]
                db_info.update({"sql": sql})
                tablefeilds = Mysql.SelectDatabase(db_info)
                tablepos = str(i) + "-" + str(table_pos)
                fields_list = []
                for field_pos in range(len(tablefeilds)):
                    fieldsinfo = {}
                    fieldpos = tablepos+"-"+str(field_pos)
                    fieldname = tablefeilds[field_pos]["Field"]
                    fieldtype = tablefeilds[field_pos]["Type"]
                    fieldlabel = fieldname + ": " + fieldtype
                    fieldsinfo["id"] = str(fieldpos)
                    fieldsinfo["parent_id"] = str(tablepos)
                    fieldsinfo["label"] = fieldname
                    fieldsinfo["children"] = [{"label": fieldtype, "dbname": db, "tablename": table_set[table_pos],
                                               "fieldname": fieldname}]
                    fields_list.append(fieldsinfo)
                    # print(type(fieldpos))
                tableinfo["id"] = str(tablepos)
                tableinfo["parent_id"] = str(i)
                tableinfo["label"] = table_set[table_pos]
                tableinfo["children"] = fields_list
                # print(type(tablepos))
                tableinfo_list.append(tableinfo)
            all_dbtables["id"] = str(i)
            all_dbtables["parent_id"] = "0"
            all_dbtables["label"] = db
            all_dbtables["children"] = tableinfo_list
            desc_tables.append(all_dbtables)
        return desc_tables

    # 主体入口
    def main(data):
        db_info = data
        db_result = Mysql.GetDatabase(db_info)
        if "False" not in db_result:
            return Mysql.GetTables(db_info, db_result)

